package com.kevin.sql;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;

import com.kevin.enums.DBType;
import com.kevin.extension.MFieldRow;
import com.kevin.table.MCellStruct;
import com.kevin.table.MDataColumn;

public class SqlFormat {
	/**
	 * Sql关键字处理
	 * @param name 数据表名称
	 * @param dbType 数据库类型
	 * @return 处理后的数据表名称
	 */
	public static String keyword(String name,DBType dbType) {
		if(StringUtils.isEmpty(name)) return name;
		name = name.trim();
		if(StringUtils.indexOfAny(name, ' ', '[', ']', '`', '"', '(', ')') > -1) return name;
		
		String pre = null;
		int i = name.lastIndexOf('.');
		if(i>0){
			String[] items = StringUtils.split(name, '.');
			pre = items[0];
			name = items[items.length - 1];
		}
		switch (dbType) {
		case ACCESS:
			name = "["+name+"]";
			break;
		case MSSQL:
		case SYBASE:
			name = (pre == null?"":pre+"..")+"["+name+"]";
			break;
		case MYSQL:
			name = (pre == null?"":pre+".")+"`" + name + "`";
			break;
		case SQLITE:
			name = "\"" + name + "\"";
			break;
		case TXT:
		case XML:
			name = notKeyword(name);
			break;
		case NONE:
			break;
		case ORACLE:
			break;
		default:
			break;
		}
		return name;
	}
	
	/**
	 * 去除关键字符号
	 * @param name 数据库表名
	 * @return 处理过的数据库表名
	 */
	public static String notKeyword(String name){	
		name = name.trim();
		if(StringUtils.indexOfAny(name, '(', ')') == -1 && StringUtils.split(name, ' ').length ==1){
			int i = StringUtils.lastIndexOf(name, '.');
			if(i>0){
				name = StringUtils.substring(name, i+1);
			}
			name = StringUtils.strip(name,"[]`\"'");
		}
		return name;
	}
	
	 /**
	  * Sql数据库兼容和Sql注入处理
	 * @param where where 对象可以是MFieldRow对象或者string对象
	 * @param dbType
	 * @param isFilterInjection
	 * @return
	 */
	public static String compatible(Object where, DBType dbType, boolean isFilterInjection)
     {
         String text = getFieldRowSql(where);
         if (isFilterInjection)
         {
             text = SqlInjection.Filter(text, dbType);
         }
         text = SqlCompatible.Format(text, dbType);

         return whereOneEqOne(text);
     }
	 
	 /**
	  * 处理where对象为MFieldRow的时候
	 * @param whereObj
	 * @return
	 */
	public static String getFieldRowSql(Object whereObj)
     {
         if (whereObj instanceof MFieldRow)
         {
        	 MFieldRow field = (MFieldRow)whereObj;
             String where = field.getSql();
             field.setSql("");
             return where;
         }
         return String.valueOf(whereObj);
     }
	 
	 /**
	  * 处理条件中存在1=1的情况
	 * @param sql
	 * @return
	 */
	public static String whereOneEqOne(String sql) {
		sql = sql.trim();
		if(sql == "where 1=1"){
			return StringUtils.EMPTY;
		}
		if(StringUtils.endsWith(sql, " and 1=1")){
			return StringUtils.substring(sql, sql.length()-8);
		}
		int i = StringUtils.indexOf(sql, "where 1=1");
		
		if(i>0){
			if(i == sql.length() -9){
				sql = StringUtils.substring(sql, sql.length() - 10);
			}else if(StringUtils.substring(sql, i+10,8).toLowerCase() == "order by"){
				//sql = StringUtils.remove(sql, i,10);
			}
		}
		return sql;
	}
	
	/**
	 * 针对Oracle数据库的时间格式
	 * @param where
	 * @param mdc
	 * @return
	 */
	public static String FormatOracleDateTime(String where, MDataColumn mdc)
    {
        if (where.indexOf(':') > -1 && StringUtils.indexOfAny(where, '>', '<', '-', '/') > -1)//判断是否存在日期的判断
        {
            for (MCellStruct item : mdc)
            {
                if ((item.getSqlType() == 93 || item.getSqlType() == 91) && where.indexOf(item.getColumnName()) > -1)
                {
                	StringBuffer sBuffer = new StringBuffer();
                	Pattern pattern = Pattern.compile("(\\s?" + item.getColumnName() + "\\s*[><]{1}[=]?\\s*)('.{19,23}')");
                    Matcher matcher = pattern.matcher(where);
                    while(matcher.find()){ 	
                    	String replaceStr = StringUtils.EMPTY;
                    	if(item.getSqlType() == 93){
                    		replaceStr = matcher.group(1) + "to_timestamp(" + matcher.group(2) + ",'yyyy-MM-dd HH24:MI:ss.ff')";
                    	}else{
                    		replaceStr = matcher.group(1) + "to_date(" + matcher.group(2) + ",'yyyy-mm-dd hh24:mi:ss')";
                    	}
                    	matcher.appendReplacement(sBuffer, replaceStr);
                    }
                    matcher.appendTail(sBuffer);
                    where = sBuffer.toString();
                }
            }
        }
        return where;
    }
}
